﻿Imports System.Data.SqlClient
Imports System.Text

Public Class RechercheSPE

    Private user As String
    Private userSite As String
    Private cn As CnData
    Private da As SqlDataAdapter
    Private ds As DataSet
    Private cmd As SqlCommand
    Private dtr As SqlDataReader

    Public Sub New(ByVal user As String, ByRef cn As CnData, ByVal userSite As String)
        ' Cet appel est requis par le concepteur.
        InitializeComponent()
        ' Ajoutez une initialisation quelconque après l'appel InitializeComponent().
        Me.user = user
        Me.cn = cn
        Me.userSite = userSite
    End Sub


    Private Sub RechercheLP_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        cn.Open()
        da = New SqlDataAdapter("SELECT '00' as NumSite,'' as NomDoc FROM Sites UNION SELECT NumSite,NomDoc FROM Sites WHERE Actif = 1", cn.getCn)
        ds = New DataSet
        da.Fill(ds, "SitesD")
        With siteE
            .DataSource = ds.Tables("SitesD")
            .DisplayMember = "NomDoc"
            .ValueMember = "NumSite"
            .SelectedValue = "00"
        End With
        da = New SqlDataAdapter("SELECT '00' as NumSite,'' as NomDoc FROM Sites UNION SELECT NumSite,NomDoc FROM Sites WHERE Actif = 1", cn.getCn)
        da.Fill(ds, "SitesD1")
        With siteD
            .DataSource = ds.Tables("SitesD1")
            .DisplayMember = "NomDoc"
            .ValueMember = "NumSite"
            .SelectedValue = "00"
        End With
        da = New SqlDataAdapter("SELECT '00' as NumFlux, '' as NomFlux FROM Flux UNION SELECT NumFlux,NomFlux FROM Flux WHERE Actif = 1 ORDER BY NomFlux", cn.getCn)
        da.Fill(ds, "Flux")
        With Typef
            .DataSource = ds.Tables("Flux")
            .DisplayMember = "NomFlux"
            .ValueMember = "NumFlux"
            .SelectedIndex = 0
        End With
        Date1.Value = Date.Today
        Date1.Checked = False

        da = New SqlDataAdapter("select TOP 10000 fin.* from ((select lpc.lp as lp,(select TypeSpe from Flux where NumFlux = lp.Flux) as type_spe, sp.spe as num_spe, (select NomFlux from Flux where NumFlux = lp.Flux) as type_flux, " _
                    & " zr.rayon as rayon, zr.info1_doc as lib_rayon, zr.info2_eti as marques, " _
                    & " (select nomdoc from sites where numsite = lp.expediteur) as de, " _
                    & " (select nomDoc from Sites where NumSite = lp.Destinataire) as vers, " _
                    & " lp.typeagres as type_agres, lp.nombrecolis as nombre_colis, lp.statut as statut_lp, " _
                    & " lpc.statut as statut_reception, lpc.anomalie as anomalie_réception, " _
                    & " lpc.chargement as chargement, " _
                    & " (select nomDoc from Sites where NumSite = ch.Expediteur) as ch_expediteur,  " _
                    & " (select nomDoc from Sites where NumSite = ch.Destinataire) as ch_destinataire,  " _
                    & " ch.dateexp as date_expedition, ch.daterec as date_reception, lp.DateImp  as date_impression, ch.statut as statut_chargement " _
                    & " from lp_chargement lpc " _
                    & " inner join chargements ch on ch.Chargement = lpc.Chargement " _
                    & " inner join LP lp on lpc.LP = lp.LP " _
                    & " left join spelp sp on lpc.lp = sp.lp " _
                    & " left join zr zr on zr.IdZR = lp.IdZR)" _
                    & " union ( " _
                    & " select lpe.lp as lp,(select TypeSpe from Flux where NumFlux = lp.Flux) as type_spe, sp.spe as num_spe,  " _
                    & " (select NomFlux from Flux where NumFlux = lp.Flux) as type_flux, " _
                    & " zr.rayon as rayon, zr.info1_doc as lib_rayon, zr.info2_eti as marques, " _
                    & " (select nomdoc from sites where numsite = lp.expediteur) as de, " _
                    & " (select nomDoc from Sites where NumSite = lp.Destinataire) as vers, " _
                    & " lp.typeagres as type_agres, lp.nombrecolis as nombre_colis, lp.statut as statut_lp, " _
                    & " null, lpe.anomalie as anomalie_reception, " _
                    & " lpe.chargement as chargement, " _
                    & " (select nomDoc from Sites where NumSite = ch.Expediteur) as ch_expediteur,  " _
                    & " (select nomDoc from Sites where NumSite = ch.Destinataire) as ch_destinataire,  " _
                    & " ch.dateexp as date_expedition, ch.daterec as date_reception, lp.DateImp  as date_impression, ch.statut as statut_chargement " _
                    & " from lp_excedent lpe " _
                    & " inner join chargements ch on ch.Chargement = lpe.Chargement " _
                    & " inner join LP lp on lpe.LP = lp.LP " _
                    & " left join spelp sp on lpe.lp = sp.lp " _
                    & " left join zr zr on zr.IdZR = lp.IdZR) " _
                    & " ) fin WHERE date_expedition > '01/01/2099' order by date_expedition desc", cn.getCn)
        'da.Fill(ds, "ListeC")
        If da.Fill(ds, "ListeC") >= 10000 Then MsgBox("Seuls les 10 000 derniers enregistrements sont affichés")
        Liste.DataSource = ds.Tables("ListeC")
        cn.Close()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.Cursor = Cursors.WaitCursor
        Dim tmp As String
        tmp = "WHERE 1=1"
        If Date1.Checked Then tmp = tmp & " AND fin.date_reception = '" & Date1.Value & "'"
        If Not String.IsNullOrEmpty(lp.Text) Then tmp = tmp & " AND fin.lp = '" & lp.Text & "'"
        If Not String.IsNullOrEmpty(spe.Text) Then tmp = tmp & " AND fin.num_spe like '%" & spe.Text & "%'"
        If siteE.SelectedValue <> "00" Then tmp = tmp & " AND fin.ch_expediteur = '" & siteE.Text & "'"
        If siteD.SelectedValue <> "00" Then tmp = tmp & " AND fin.ch_destinataire = '" & siteD.Text & "'"
        If Not String.IsNullOrEmpty(statut.Text) Then tmp = tmp & " AND fin.statut_lp = '" & statut.Text & "'"
        If Not String.IsNullOrEmpty(rayon.Text) Then tmp = tmp & " AND fin.rayon = '" & rayon.Text & "'"
        If Typef.SelectedValue <> "00" Then tmp = tmp & " AND fin.type_flux = '" & Typef.Text & "'"
        'MsgBox(tmp)
        da = New SqlDataAdapter("select top 10000 fin.* from (select lpc.lp as lp,(select TypeSpe from Flux where NumFlux = lp.Flux) as type_spe, sp.spe as num_spe, (select NomFlux from Flux where NumFlux = lp.Flux) as type_flux, " _
                    & " zr.rayon as rayon, zr.info1_doc as lib_rayon, zr.info2_eti as marques, " _
                    & " (select nomdoc from sites where numsite = lp.expediteur) as de, " _
                    & " (select nomDoc from Sites where NumSite = lp.Destinataire) as vers, " _
                    & " lp.typeagres as type_agres, lp.nombrecolis as nombre_colis, lp.statut as statut_lp, " _
                    & " lpc.statut as statut_reception, lpc.anomalie as anomalie_réception, " _
                    & " lpc.chargement as chargement, " _
                    & " (select nomDoc from Sites where NumSite = ch.Expediteur) as ch_expediteur,  " _
                    & " (select nomDoc from Sites where NumSite = ch.Destinataire) as ch_destinataire,  " _
                    & " ch.dateexp as date_expedition, ch.daterec as date_reception,  lp.DateImp  as date_impression, ch.statut as statut_chargement" _
                    & " from lp_chargement lpc " _
                    & " inner join chargements ch on ch.Chargement = lpc.Chargement " _
                    & " inner join LP lp on lpc.LP = lp.LP " _
                    & " left join spelp sp on lpc.lp = sp.lp " _
                    & " left join zr zr on zr.IdZR = lp.IdZR " _
                    & " union ( " _
                    & " select lpe.lp as lp,(select TypeSpe from Flux where NumFlux = lp.Flux) as type_spe, sp.spe as num_spe,  " _
                    & " (select NomFlux from Flux where NumFlux = lp.Flux) as type_flux, " _
                    & " zr.rayon as rayon, zr.info1_doc as lib_rayon, zr.info2_eti as marques, " _
                    & " (select nomdoc from sites where numsite = lp.expediteur) as de, " _
                    & " (select nomDoc from Sites where NumSite = lp.Destinataire) as vers, " _
                    & " lp.typeagres as type_agres, lp.nombrecolis as nombre_colis, lp.statut as statut_lp, " _
                    & " null, lpe.anomalie as anomalie_reception, " _
                    & " lpe.chargement as chargement, " _
                    & " (select nomDoc from Sites where NumSite = ch.Expediteur) as ch_expediteur,  " _
                    & " (select nomDoc from Sites where NumSite = ch.Destinataire) as ch_destinataire,  " _
                    & " ch.dateexp as date_expedition, ch.daterec as date_reception,lp.DateImp as date_impression, ch.statut as statut_chargement " _
                    & " from lp_excedent lpe " _
                    & " inner join chargements ch on ch.Chargement = lpe.Chargement " _
                    & " inner join LP lp on lpe.LP = lp.LP " _
                    & " left join spelp sp on lpe.lp = sp.lp " _
                    & " left join zr zr on zr.IdZR = lp.IdZR) " _
                    & " ) fin " & tmp & "  order by date_expedition desc", cn.getCn)
        'da.Fill(ds, "ListeC")
        ds.Tables("ListeC").Clear()
        If da.Fill(ds, "ListeC") >= 10000 Then
            'Me.Cursor = Cursors.Default
            MsgBox("Seuls les 10 000 derniers enregistrements sont affichés")
        End If
        Me.Cursor = Cursors.Default
    End Sub

    Private Sub recherche_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles recherche.KeyPress
        If e.KeyChar = Chr(34) Or e.KeyChar = "'" Or e.KeyChar = "*" Then
            e.KeyChar = ""
            Exit Sub
        End If
    End Sub

    Private Sub recherche_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles recherche.TextChanged
        Dim retrdvview As New DataView
        On Error GoTo Err
        With retrdvview
            .Table = ds.Tables("ListeC")
            .RowFilter = "[num_spe]+' '+[lp]+' '+[type_flux]+' '+[date_impression] like '%" & recherche.Text & "%'"
        End With
        Liste.DataSource = retrdvview
        Exit Sub
Err:
        MsgBox("Saisie incorrecte")
        recherche.Text = ""
    End Sub

    Private Sub Fermer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Fermer.Click
        Me.Close()
    End Sub

    Private Sub spe_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles spe.KeyPress
        If e.KeyChar = Chr(34) Or e.KeyChar = "'" Or e.KeyChar = "*" Then
            e.KeyChar = ""
            Exit Sub
        End If
    End Sub

    Private Sub lp_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles lp.KeyPress
        If e.KeyChar = Chr(34) Or e.KeyChar = "'" Or e.KeyChar = "*" Then
            e.KeyChar = ""
            Exit Sub
        End If
    End Sub

    Private Sub rayon_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles rayon.KeyPress
        If e.KeyChar = Chr(34) Or e.KeyChar = "'" Or e.KeyChar = "*" Then
            e.KeyChar = ""
            Exit Sub
        End If
    End Sub

    Private Sub ExtraireButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExtraireButton.Click
        Try
            'Choix(d) 'un fichier de destination
            If SaveFileDialog1.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
                'On valide l'édition de la DataGridView
                Liste.EndEdit()
                'On se prépare une mémoire des données formatées a écrire dans le fichier
                Dim ToSave As New StringBuilder()
                ' On vas y mettre les en têtes tant demandées :-)
                Dim Headers As String = String.Empty
                For index As Integer = 0 To Liste.Columns.Count - 1

                    Headers &= Liste.Columns(index).HeaderText & ";"

                Next
                ' La boucle ajoute un ";" a la fin qui est inutile
                Headers = Headers.Remove(Headers.LastIndexOf(";"), 1)
                'Maintenant(qu) 'il est "propre" on le stocke dans la mémoire
                ToSave.AppendLine(Headers)

                ' On boucle sur toutes les lignes disponibles
                For i As UInt64 = 0 To Liste.Rows.Count - 1
                    'On se fait une variable pour y stocké une ligne
                    Dim OneRow As String = String.Empty
                    'On peut faire une boucle sur toutes les colonnes disponible si la ligne n'est pas vide
                    If Liste.Rows(i).IsNewRow = False Then
                        For j As Integer = 0 To Liste.Rows(i).Cells.Count - 1
                            OneRow &= Liste.Rows(i).Cells(j).Value & ";"
                        Next
                        'La boucle ajoute un ";" a la fin qui est inutile
                        OneRow = OneRow.Remove(OneRow.LastIndexOf(";"), 1)
                        'Maintenant(qu) 'il est "propre" on le stocke dans la mémoire
                        ToSave.AppendLine(OneRow)
                    End If
                Next
                'Tout est bien qui finit bien ? essayons maintenant d'écrire le fichier
                IO.File.WriteAllText(SaveFileDialog1.FileName, ToSave.ToString(), Encoding.Default)

            End If

        Catch ex As Exception
            MessageBox.Show(String.Format("{0}{1}{1}{2}", ex.Message, Environment.NewLine, ex.StackTrace))
        End Try

        'Dim c, ligne As Integer
        'Dim xl As New Microsoft.Office.Interop.Excel.Application


        'xl.Visible = False
        'xl.Workbooks.Add()
        'xl.Worksheets(1).Range("A1:S1").Font.bold = True
        'xl.Worksheets(1).Columns("A").NumberFormat = "@"
        'xl.Worksheets(1).Columns("M").NumberFormat = "@"

        'Try
        '    For ligne = 0 To Liste.Rows.Count - 1
        '        For c = 0 To Liste.Columns.Count - 1
        '            xl.Cells(1, c + 1) = Liste.Columns(c).Name
        '            xl.Cells(ligne + 2, c + 1) = Liste.Rows(ligne).Cells(c).Value
        '            xl.Range("A1:S1").EntireColumn.Columns.AutoFit()
        '        Next (c)
        '    Next ligne

        '    xl.Visible = True
        'Catch ex As Exception
        '    MsgBox(ex.Message)
        'End Try

        'xl = Nothing
        'GC.Collect()
        'GC.WaitForPendingFinalizers()

    End Sub
End Class